<?php

if (!defined('BASEPATH'))
	exit('No direct script access allowed');

class Reservasi_model extends CI_Model {

	function __construct() {
		parent::__construct();
	}

	function get_all($date='') {

		if ($date == '') {
			$date = date('Ymd');
		} else {
			$date = $this->date_func->change_format_date($date, 'd-m-Y', 'Ymd');
		}
		$this->db->distinct();
		$this->db->select('NoSPJ');
		$this->db->select('NoPol');
		$this->db->select('Driver');
		$this->db->select('JamBerangkat');
		$this->db->select('Asal');
		$this->db->select('Tujuan');
		$this->db->where('TglBerangkat', $date);
		//$this->db->where('NoSPJ!=', ' ');
		return $this->db->get('TbReservasi')->result_array();
	}
        
        function get_all_for_kasir($kota,$date='') {

		if ($date == '') {
			$date = date('Ymd');
		} else {
			$date = $this->date_func->change_format_date($date, 'd-m-Y', 'Ymd');
		}
		$this->db->distinct();
		$this->db->select('NoSPJ');
		$this->db->select('NoPol');
		$this->db->select('Driver');
		$this->db->select('JamBerangkat');
		$this->db->select('Asal');
		$this->db->select('Tujuan');
                $this->db->where('TglBerangkat = '.$date.' AND (Asal = "'.$kota.'" OR Tujuan = "'.$kota.'")');
		//$this->db->where('NoSPJ!=', ' ');
		return $this->db->get('TbReservasi')->result_array();
	}
        
        function get_all_for_kasir_tujuan($kota,$date='') {

		if ($date == '') {
			$date = date('Ymd');
		} else {
			$date = $this->date_func->change_format_date($date, 'd-m-Y', 'Ymd');
		}
		$this->db->distinct();
		$this->db->select('NoSPJ');
		$this->db->select('NoPol');
		$this->db->select('Driver');
		$this->db->select('JamBerangkat');
		$this->db->select('Asal');
		$this->db->select('Tujuan');
                $this->db->where('TglBerangkat',$date);
                $this->db->where('Tujuan',$kota);
		//$this->db->where('NoSPJ!=', ' ');
		return $this->db->get('TbReservasi')->result_array();
	}
        
        function get_all_for_kasir_asal($kota,$date='') {

		if ($date == '') {
			$date = date('Ymd');
		} else {
			$date = $this->date_func->change_format_date($date, 'd-m-Y', 'Ymd');
		}
		$this->db->distinct();
		$this->db->select('NoSPJ');
		$this->db->select('NoPol');
		$this->db->select('Driver');
		$this->db->select('JamBerangkat');
		$this->db->select('Asal');
		$this->db->select('Tujuan');
                $this->db->where('TglBerangkat',$date);
                $this->db->where('Asal',$kota);
		//$this->db->where('NoSPJ!=', ' ');
		return $this->db->get('TbReservasi')->result_array();
	}

	function get_one($noSPJ) {
                $this->db->distinct();
		$this->db->select('NoSPJ');
		$this->db->select('NoPol');
		$this->db->select('Driver');
		$this->db->select('JamBerangkat');
		$this->db->select('Asal');
                $this->db->select('TGLSPJ');
		$this->db->select('Tujuan');
                $this->db->select('KodeCabang');
		$query = $this->db->get_where('TbReservasi', array('NoSPJ' => $noSPJ));
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return FALSE;
		}
	}

	function get_all_setoran($date='') {
		if ($date == '') {
			$date = date('Ymd');
		} else {
			$date = $this->date_func->change_format_date($date, 'd-m-Y', 'Ymd');
		}
		$this->db->where('tanggal', $date);
		$this->db->where('is_closing!=', 1);
		return $this->db->get('TblSetoran')->result_array();
	}
        
        function get_all_setoran_for_kasir($kota,$date='') {
		if ($date == '') {
			$date = date('Ymd');
		} else {
			$date = $this->date_func->change_format_date($date, 'd-m-Y', 'Ymd');
		}
		$this->db->where('tanggal', $date);
                $this->db->where('asal = "'.$kota.'" OR tujuan = "'.$kota.'"');
		$this->db->where('is_closing!=', 1);
		return $this->db->get('TblSetoran')->result_array();
	}
        
        function get_all_setoran_for_kasir_tujuan($kota,$date='') {
		if ($date == '') {
			$date = date('Ymd');
		} else {
			$date = $this->date_func->change_format_date($date, 'd-m-Y', 'Ymd');
		}
		$this->db->where('tanggal', $date);
                $this->db->where('tujuan',$kota);
		$this->db->where('is_closing!=', 1);
		return $this->db->get('TblSetoran')->result_array();
	}

	function get_one_setoran($noSPJ) {
		$query = $this->db->get_where('TblSetoran', array('NoSPJ' => $noSPJ));
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return FALSE;
		}
	}

        function get_id_setoran_bank_by_setoran($kota,$date='')
        {
            if ($date == '') {
			$date = date('Ymd');
		} else {
			$date = $this->date_func->change_format_date($date, 'd-m-Y', 'Ymd');
		}
                $this->db->where('tanggal', $date);
                $this->db->where('tujuan',$kota);
                $this->db->group_by('idsetoranBank');
                $this->db->select('idsetoranBank');
		return $this->db->get('TblSetoran')->result_array();
        }
        
	function get_all_setoran_bank($date='') {
		if ($date == '') {
			$date = date('Ymd');
		} else {
			$date = $this->date_func->change_format_date($date, 'd-m-Y', 'Ymd');
		}
		$this->db->where('tanggal', $date);
		return $this->db->get('TblSetoranBank')->result_array();
	}
        
        function get_one_setoran_bank($id) {
		$query = $this->db->get_where('TblSetoranBank', array('id' => $id));
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return FALSE;
		}
	}
        
        function get_all_setoran_bank_for_kasir($kota,$date='') {
		$result = $this->get_id_setoran_bank_by_setoran($kota, $date);
                $array = array();
                for($i=0;$i<count($result);$i++)
                {
                    $array[$i] = $this->get_one_setoran_bank($result[$i]['idsetoranBank']);
                }
		return $array;
	}

	function get_setoran_bank_join_user($tgl_awal, $tgl_akhir) {
		if ($tgl_awal == '') {
			$tgl_awal = date('Ymd');
		} else {
			$tgl_awal = $this->date_func->change_format_date($tgl_awal, 'd-m-Y', 'Ymd');
		}
		if ($tgl_akhir == '') {
			$tgl_akhir = date('Ymd');
		} else {
			$tgl_akhir = $this->date_func->change_format_date($tgl_akhir, 'd-m-Y', 'Ymd');
		}
		$this->db->join('TbUser', 'TbUser.user_id = TblSetoranBank.idkasir', 'inner');
		$this->db->where('tanggal BETWEEN "' . $tgl_awal . '" AND "' . $tgl_akhir . '"');
		return $this->db->get('TblSetoranBank')->result_array();
	}

	function get_setoran_bank_join_user_by_cabang($tgl_awal, $tgl_akhir, $cabang) {
		if ($tgl_awal == '') {
			$tgl_awal = date('Ymd');
		} else {
			$tgl_awal = $this->date_func->change_format_date($tgl_awal, 'd-m-Y', 'Ymd');
		}
		if ($tgl_akhir == '') {
			$tgl_akhir = date('Ymd');
		} else {
			$tgl_akhir = $this->date_func->change_format_date($tgl_akhir, 'd-m-Y', 'Ymd');
		}
		$this->db->join('TbUser', 'TbUser.user_id = TblSetoranBank.idkasir', 'inner');
		$this->db->where('tanggal BETWEEN "' . $tgl_awal . '" AND "' . $tgl_akhir . '"');
		$this->db->where('cabang_keberangkatan', $cabang);
		return $this->db->get('TblSetoranBank')->result_array();
	}

	function get_setoran_by_setoran_bank($idsetoranBank) {
		return $this->db->get_where('TblSetoran', array('idsetoranBank' => $idsetoranBank))->result_array();
	}

	function get_setoran_join_user($idsetoranBank) {
		$this->db->join('TbUser', 'TbUser.user_id = TblSetoran.idkasirSetoran', 'inner');
		$query = $this->db->get_where('TblSetoran', array('idsetoranBank' => $idsetoranBank));
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return FALSE;
		}
	}

	function get_all_setoran_join_user($idsetoranBank, $date='') {
//		if ($date == '') {
//			$date = date('Ymd');
//		} else {
//			$date = $this->date_func->change_format_date($date, 'd-m-Y', 'Ymd');
//		}
		$this->db->join('TbUser', 'TbUser.user_id = TblSetoran.idkasirSetoran', 'inner');
		$query = $this->db->get_where('TblSetoran', array('idsetoranBank' => $idsetoranBank));
//		$this->db->where('tanggal', $date);
		return $query->result_array();
	}

	function get_biaya_by_reservasi($nospj) {
                $this->db->order_by("idbiaya", "asc");
		return $this->db->get_where('TblBiaya', array('NoSPJ' => $nospj, 'is_tujuan'=>'0'))->result_array();
	}

	function get_subtotal($noSPJ) {
		$this->db->select_sum('SubTotal');
		$query = $this->db->get_where('TbReservasi', array('NoSPJ' => $noSPJ));
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return FALSE;
		}
	}

	function get_discount($noSPJ) {
		$this->db->select_sum('Discount');
		$query = $this->db->get_where('TbReservasi', array('NoSPJ' => $noSPJ));
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return FALSE;
		}
	}

	function get_chargeA($noSPJ) {
		$this->db->select_sum('ChargeA');
		$query = $this->db->get_where('TbReservasi', array('NoSPJ' => $noSPJ));
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return FALSE;
		}
	}

	function get_chargeB($noSPJ) {
		$this->db->select_sum('ChargeB');
		$query = $this->db->get_where('TbReservasi', array('NoSPJ' => $noSPJ));
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return FALSE;
		}
	}

	function get_jumlah_seat($noSPJ) {
		$this->db->distinct();
		$query = $this->db->get_where('TbReservasi', array('NoSPJ' => $noSPJ));
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return FALSE;
		}
	}

	function get_jumlah_penumpang($noSPJ) {
		$this->db->select_sum('PesanKursi');
		$query = $this->db->get_where('TbReservasi', array('NoSPJ' => $noSPJ));
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return FALSE;
		}
	}

	function get_fee_driver($asal, $tujuan) {
		$query = $this->db->get_where('TblFeeDriver', array('asal' => $asal, 'tujuan' => $tujuan));
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return FALSE;
		}
	}

	function get_penerimaan_jurusan($asal, $tujuan) {
		$this->db->select("SUM(total_penumpang) AS total_penumpang,SUM(tiket_void) AS tiket_void,SUM(total_omset) AS total_omset,
                SUM(total_discount) AS total_discount,SUM(fee_agen) AS fee_agen,SUM(total_void) AS total_void, SUM(jumlah) AS jumlah,
                SUM(total_setoran) AS total_setoran, SUM(total_laba) AS total_laba, SUM(total_kapasitas) AS total_kapasitas");
		$this->db->join('TblBiaya', 'TblBiaya.NoSPJ = TblSetoran.NoSPJ', 'inner');
		$this->db->where('asal', $asal);
		$this->db->where('tujuan', $tujuan);
		$this->db->where('is_tujuan', 1);
		$query = $this->db->get('TblSetoran');
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return FALSE;
		}
	}

	function get_op_awal($asal, $tujuan) {
		$this->db->select("SUM(jumlah) AS jumlah");
		$this->db->join('TblBiaya', 'TblBiaya.NoSPJ = TblSetoran.NoSPJ', 'inner');
		$this->db->where('asal', $asal);
		$this->db->where('tujuan', $tujuan);
		$this->db->where('is_tujuan', 0);
		$query = $this->db->get('TblSetoran');
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return FALSE;
		}
	}

	function get_total_keberangkatan_penerimaan_jurusan($asal, $tujuan) {
		$this->db->select("COUNT(id) AS jumlah_keberangkatan");
		if ($asal != '')
			$this->db->where('asal', $asal);
		if ($tujuan != '')
			$this->db->where('tujuan', $tujuan);
		$query = $this->db->get('TblSetoran');
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return FALSE;
		}
	}
        
        function get_cabang_by_user($cabang) {
                $this->db->where('Kode', $cabang);
		$query = $this->db->get('TbMDCabang');
		if ($query->num_rows() > 0) {
			return $query->row_array();
		} else {
			return FALSE;
		}
	}

	function get_jurusan($tgl_awal, $tgl_akhir, $asal, $tujuan) {
		if ($tgl_awal == '') {
			$tgl_awal = date('Ymd');
		} else {
			$tgl_awal = $this->date_func->change_format_date($tgl_awal, 'd-m-Y', 'Ymd');
		}
		if ($tgl_akhir == '') {
			$tgl_akhir = date('Ymd');
		} else {
			$tgl_akhir = $this->date_func->change_format_date($tgl_akhir, 'd-m-Y', 'Ymd');
		}
		$this->db->where('tanggal BETWEEN "' . $tgl_awal . '" AND "' . $tgl_akhir . '"');
		if ($asal != 'SEMUA')
			$this->db->where('asal', $asal);
		if ($tujuan != 'SEMUA')
			$this->db->where('tujuan', $tujuan);
		$this->db->distinct();
		$this->db->select('asal,tujuan');
		return $this->db->get('TblSetoran')->result_array();
	}

	function get_asal_tujuan() {
		//$this->db->order_by('title desc, name asc'); 
		return $this->db->get('TbMDKota')->result_array();
	}
        
        function get_asal_tujuan_for_kasir($kota) {
		//$this->db->order_by('title desc, name asc');
                $this->db->where('Nama',$kota);
		return $this->db->get('TbMDKota')->result_array();
	}

	function insert_setoran($data) {
		$this->db->insert('TblSetoran', $data);
	}

	function insert_biaya($data) {
		$this->db->insert('TblBiaya', $data);
	}
        function update_biaya($id,$data) {
                $this->db->where('id',$id);
		$this->db->update('TblBiaya',$data);
	}
        

	function insert_setoran_bank($data) {
		$this->db->insert('TblSetoranBank', $data);
	}

	function insert_spbu_voucher($data) {
		$this->db->insert('TblSPBUVoucher', $data);
	}
        
        function update_spbu_voucher($no_spj,$data) {
                $this->db->where('NoSPJ',$no_spj);
		$this->db->update('TblSPBUVoucher',$data);
	}

	function get_max_id_setoran() {
		$this->db->select_max('id');
		return $this->db->get('TblSetoran')->row_array();
	}

	function get_max_id_setoran_bank() {
		$this->db->select_max('id');
		return $this->db->get('TblSetoranBank')->row_array();
	}

	function sudah_setor($no_spj) {
		$query = $this->db->get_where('TblSetoran', array(
			'NoSPJ' => $no_spj
				));
		if ($query->num_rows == 1) {
			return TRUE;
		} else {
			return FALSE;
		}
	}
        
        function sudah_bayar($no_spj) {
		$query = $this->db->get_where('TblBiaya', array(
			'NoSPJ' => $no_spj,
                        'is_tujuan' => 0
				));
		if ($query->num_rows > 0) {
			return TRUE;
		} else {
			return FALSE;
		}
	}
        
        function sudah_spbu_voucher($no_spj) {
		$query = $this->db->get_where('TblSPBUVoucher', array(
			'NoSPJ' => $no_spj
				));
		if ($query->num_rows > 0) {
			return TRUE;
		} else {
			return FALSE;
		}
	}

	function update_setoran($nospj, $data) {
		$this->db->where('NoSPJ', $nospj);
		$this->db->update('TblSetoran', $data);
	}

}

/** End of file */